-- MedAssist Clinic Management System
-- Primary keys, foreign keys and check constraints
USE `medassets`;

ALTER TABLE `appointment`
  ADD PRIMARY KEY (`AppointmentID`),
  ADD KEY `idx_appointment_patient` (`PatientID`),
  ADD KEY `idx_appointment_doctor_date` (`DoctorID`,`AppointmentDate`),
  ADD KEY `idx_appointment_date` (`AppointmentDate`);

ALTER TABLE `auditlog`
  ADD PRIMARY KEY (`AuditID`);

ALTER TABLE `department`
  ADD PRIMARY KEY (`DepartmentID`);

ALTER TABLE `doctor`
  ADD PRIMARY KEY (`DoctorID`),
  ADD KEY `DepartmentID` (`DepartmentID`);

ALTER TABLE `feedback`
  ADD PRIMARY KEY (`FeedbackID`),
  ADD KEY `feedback_ibfk_1` (`PatientID`),
  ADD KEY `feedback_ibfk_2` (`AppointmentID`);

ALTER TABLE `patient`
  ADD PRIMARY KEY (`PatientID`),
  ADD KEY `idx_patient_name` (`LastName`,`FirstName`);

ALTER TABLE `treatment`
  ADD PRIMARY KEY (`TreatmentID`),
  ADD UNIQUE KEY `AppointmentID` (`AppointmentID`),
  ADD KEY `idx_treatment_appointment` (`AppointmentID`);

ALTER TABLE `appointment`
  MODIFY `AppointmentID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=46;

ALTER TABLE `auditlog`
  MODIFY `AuditID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

ALTER TABLE `department`
  MODIFY `DepartmentID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

ALTER TABLE `doctor`
  MODIFY `DoctorID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;

ALTER TABLE `feedback`
  MODIFY `FeedbackID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

ALTER TABLE `patient`
  MODIFY `PatientID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=35;

ALTER TABLE `treatment`
  MODIFY `TreatmentID` int NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=43;

ALTER TABLE `appointment`
  ADD CONSTRAINT `appointment_ibfk_1` FOREIGN KEY (`PatientID`) REFERENCES `patient` (`PatientID`) ON DELETE CASCADE,
  ADD CONSTRAINT `appointment_ibfk_2` FOREIGN KEY (`DoctorID`) REFERENCES `doctor` (`DoctorID`);

ALTER TABLE `doctor`
  ADD CONSTRAINT `doctor_ibfk_1` FOREIGN KEY (`DepartmentID`) REFERENCES `department` (`DepartmentID`);

ALTER TABLE `feedback`
  ADD CONSTRAINT `feedback_ibfk_1` FOREIGN KEY (`PatientID`) REFERENCES `patient` (`PatientID`) ON DELETE CASCADE,
  ADD CONSTRAINT `feedback_ibfk_2` FOREIGN KEY (`AppointmentID`) REFERENCES `appointment` (`AppointmentID`) ON DELETE CASCADE;

ALTER TABLE `treatment`
  ADD CONSTRAINT `treatment_ibfk_1` FOREIGN KEY (`AppointmentID`) REFERENCES `appointment` (`AppointmentID`) ON DELETE CASCADE;

-- Note: The original database dump did not contain explicit CHECK constraints.
-- Therefore none have been added here.
